package oas.db.jdbc;

import oas.db.UserRepository;
import oas.db.WorkcellRepository;
import oas.domain.User;

import oas.domain.Workcell;
import oas.web.PaginationSupport;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.security.crypto.factory.PasswordEncoderFactories;
import org.springframework.security.crypto.password.Md4PasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Repository;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 用户DAO实现类
 *
 * @author liyue
 * @version v1.0.0
 */
@Repository
public class JdbcUserRepository implements UserRepository {

    private final JdbcTemplate jdbcTemplate;

    private PasswordEncoder encoder = PasswordEncoderFactories.createDelegatingPasswordEncoder();

    private static final Log log = LogFactory.getLog(JdbcUserRepository.class);

    @Autowired
    private WorkcellRepository workcellRepository;

    /**
     * 注入jdbcTemplate
     *
     * @param jdbcTemplate jdbcTemplate
     */
    @Autowired
    public JdbcUserRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 通过id查找用户
     *
     * @param id 用户id
     * @return 查询结果
     */
    @Override
    public User findById(int id) {
        User user = null;
        try {
            user = jdbcTemplate.queryForObject(SELECT_USER_BY_ID, new UserRowMapper(), id);
            user.setWorkcell(workcellRepository.findById(user.getWorkcell().getId()));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 通过用户名查询用户
     *
     * @param userName 用户名
     * @return 查询结果
     */
    @Override
    public User findByUserName(String userName) {
        User user = null;
        try {
            user = jdbcTemplate.queryForObject(SELECT_USER, new UserRowMapper(), userName);
            user.setWorkcell(workcellRepository.findById(user.getWorkcell().getId()));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 通过用户名和密码查询用户
     *
     * @param userName 用户名
     * @param password 密码
     * @return 查询结果
     */
    @Override
    public User findByUserName(String userName, String password) {
        User user = null;
        Logger.getLogger("oas").debug("password: " + password);
        try {

            String checkPwd = jdbcTemplate.queryForObject(SELECT_USER_PWD, String.class, userName);
            log.debug(checkPwd);
            log.debug(encoder.encode(password));
            if (encoder.matches(password, checkPwd)) {
                user = jdbcTemplate.queryForObject(SELECT_USER
                        , new UserRowMapper(), userName);
                user.setWorkcell(workcellRepository.findById(user.getWorkcell().getId()));
            }

        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 更新用户个人信息
     *
     * @param user 用户新个人信息
     */
    @Override
    public void updateUser(User user) {
        jdbcTemplate.update(UPDATE_USER, user.getFullName(), user.getEmail(),
                DETransformGender(user.getGender()), user.getPhone(), user.getAge(), user.getId());
    }

    /**
     * 修改密码
     *
     * @param id          用户名id
     * @param oldPassword 原密码
     * @param NewPassword 新密码
     * @return 修改结果
     */
    @Override
    public boolean changePassword(Long id, String oldPassword, String NewPassword) {
        String checkPwd = jdbcTemplate.queryForObject(SELECT_OLDPWD, new PwdRowMapper(), id);
        NewPassword = encoder.encode(NewPassword);
        if (encoder.matches(oldPassword, checkPwd)) {
            jdbcTemplate.update(UPDATE_PWD, NewPassword, id);
            return true;
        }
        return false;
    }

    /**
     * 添加一个员工
     */
    @Override
    public User addEmployee(User employee, String password) {
        password = encoder.encode(password);
        jdbcTemplate.update(ADD_EMPLOYEE,
                employee.getUsername(),
                password,
                employee.getFullName(),
                employee.getAge(),
                employee.getWorkcell().getId(),
                DETransformGender(employee.getGender()),
                employee.getEmail(),
                employee.getPhone(),
                DETransformRole(employee.getRole()));

        return findByUserName(employee.getUsername());
    }

    /**
     * 根据ID删除一个员工
     */
    @Override
    public void deleteEmployeeByID(Long ID) {
        jdbcTemplate.update(DELETE_EMPLOYEE, ID);
    }

    /**
     * 修改员工信息
     */
    @Override
    public User updateEmployee(User employee) {
        log.debug(employee.getId());
        jdbcTemplate.update(UPDATE_EMPLOYEE,
                employee.getFullName(),
                DETransformGender(employee.getGender()),
                employee.getEmail(),
                employee.getPhone(),
                employee.getAge(),
                employee.getId());
        return employee;
    }

    /**
     * 依据员工ID查找某一员工
     */
    @Override
    public User findEmployee(Long id) {
        User employee = null;
        System.out.println("id: " + id);
        employee = jdbcTemplate.queryForObject(SELECT_EMPLOYEE, new UserRowMapper(), id);
        System.out.println("employee: " + employee);

        return employee;
    }

    /**
     * 返回部门及其子部门所有员工
     */
    @Override
    public List<User> findWorkcellEmployees(User employee) {
        Long id = employee.getId();
        List<User> employees = jdbcTemplate.query("select id, username, fullName, workcellId," + " where id in "
                        + "(select childWorkcellId from workcellRelation where parentWorkcellId= ?) and available =1",
                new UserRowMapper(), id);
        return employees;
    }

    /**
     * 分页显示员工列表
     */
    @Override
    public PaginationSupport<User> findEmployeePage(User employee, int pageNo, int pageSize) {

        Workcell workcell = employee.getWorkcell();
        WorkcellRepository workcellRepository = new JdbcWorkcellRepository(jdbcTemplate);

        List<Workcell> workcells = new ArrayList<Workcell>();
        workcells.add(0, employee.getWorkcell());

        List<User> employeeList = new Vector<User>();
        Queue<Workcell> queue = new LinkedList<Workcell>(workcells);
        Workcell cw;
        List<Workcell> temp;
        while (!queue.isEmpty()) {
            cw = queue.poll();
            temp = workcellRepository.findWorkcells(cw);
            workcells.addAll(temp);
            queue.addAll(temp);
        }
        workcell.setChildWorkcells(workcells);
        employee.setWorkcell(workcell);
        for (Workcell item : workcells) {
            employeeList.addAll(jdbcTemplate.query(SELECT_EMPLOYEE_BY_WID, new WCUserRowMapper(), item.getId(), item.getId()));
        }
        int totalCount = employeeList.size();//employees.size();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1)
            return new PaginationSupport<User>(new ArrayList<User>(0), 0);
        List<User> pageList = employeeList.subList(startIndex, Math.min((startIndex + pageSize), totalCount));
        PaginationSupport<User> ps = new PaginationSupport<User>(pageList, totalCount, pageSize, startIndex);

        return ps;
    }

    /**
     * 将数据库中Long类型的role转换为String类型存储到对象中
     *
     * @param role Long类型的role
     * @return String类型的role
     */
    private static String transformRole(Long role) {
        if (role == 0L) {
            return "User";
        } else if (role == 1L) {
            return "Manager";
        } else {
            return null;
        }
    }

    /**
     * 将数据库中String类型的role转换为String类型存储到对象中
     *
     * @param role String类型的role
     * @return Long类型的role
     */
    private static Long DETransformRole(String role) {
        if ("User".equals(role)) {
            return 0L;
        } else if ("Manager".equals(role)) {
            return 1L;
        } else {
            return null;
        }
    }

    /**
     * 将数据库中Long类型的gender转换为String类型存储到对象中
     *
     * @param gender Long类型的gender
     * @return String类型的gender
     */
    private static String transformGender(Long gender) {
        if (gender == 0L) {
            return "Male";
        } else return "Female";
    }

    /**
     * 将对象中String类型的gender转换为Long类型存储到数据库中
     *
     * @param gender String类型gender
     * @return Long类型gender
     */
    private static Long DETransformGender(String gender) {
        if ("Male".equals(gender)) {
            return 0L;
        } else return 1L;
    }

    /**
     * 使用RowMapper处理从数据库取出的密码
     */
    private static class PwdRowMapper implements RowMapper<String> {
        @Override
        public String mapRow(ResultSet resultSet, int i) throws SQLException {
            return resultSet.getString("pwd");
        }
    }

    private static class WCUserRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet resultSet, int i) throws SQLException {

            Long id = resultSet.getLong("id");
            String username = resultSet.getString("username");
            String gender = resultSet.getString("gender");
            String email = resultSet.getString("email");
            String phone = resultSet.getString("phone");
            int age = resultSet.getInt("age");
            String role = resultSet.getString("role");
            String fullName = resultSet.getString("fullName");
            Long wid = resultSet.getLong("wid");
            String name = resultSet.getString("name");
            User user = null;
            user = new User(id, username, fullName, new Workcell(wid, name, true, null), gender, email, phone, age, role, true);

            return user;
        }
    }

    /**
     * 使用RowMapper处理从数据库取出的用户信息
     */
    private static class UserRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet resultSet, int i) throws SQLException {

            Workcell workcell = new Workcell(resultSet.getLong("workcellId"), null, false, null);

            return new User(resultSet.getLong("id"), resultSet.getString("username"),
                    resultSet.getString("fullName"), workcell,
                    transformGender(resultSet.getLong("gender")), resultSet.getString("email"),
                    resultSet.getString("phone"), ((Long) resultSet.getLong("age")).intValue(),
                    transformRole(resultSet.getLong("role")), true);
        }
    }

    private static final String SELECT_USER = "select id, username, fullName, age, workcellId, gender, " +
            "email, phone, role from user where username = ? ";

    private static final String SELECT_USER_PWD = "select pwd " +
            " from user where username = ? ";

    private static final String UPDATE_USER = "update user " +
            "set fullName=?, email=?, gender=?, phone=?, age=? where id=? and available = 1 ";

    private static final String SELECT_OLDPWD = "select pwd from user where id = ?";

    private static final String UPDATE_PWD = "update user set pwd = ? where id = ?";

    //员工管理类sql语句
    private static final String SELECT_EMPLOYEE = "select id, username, fullName, age, workcellId, gender,email, phone, role from user "
            + "where id=? and available=1";
    private static final String SELECT_USER_BY_ID = "select id, username, fullName, age, workcellId, gender, " +
            "email, phone, role from user where id=?";

    //员工管理类sql语句
    private static final String ADD_EMPLOYEE = "insert into user(username, pwd, fullName, age, workcellId, gender, email, phone, role)" +
            "values(?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String DELETE_EMPLOYEE = "update user set available=0 where id=?";
    private static final String UPDATE_EMPLOYEE = "update user set fullname=?,gender=?,email=?,phone=?,age=? " +
            "where id=?";

    private static final String SELECT_EMPLOYEE_BY_WID = "select u.id, u.username, u.gender, u.email, u.phone, u.age, u.role, u.fullname, " +
            " w.id as wid, w.name" +
            " from user as u, workcell as w " +
            " where u.workcellid = ? and w.id = ? and u.available=1";

}
